"""
#!/usr/bin/python3
# coding:utf-8            
#
# Copyright (C) 2024 - 2024 Jasonakeke， Inc. All Rights Reserved 
# @Desc	   ：
# @Time    : 2024/8/15 22:43
# @Author  : Code_By_Jasonakeke
# @Email   : 2284037977@qq.com
# @File    : 03-数据的CRUD操作.py
# @IDE     : PyCharm
"""
from sqlalchemy import create_engine, Column, Integer, String, and_
from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy.sql.functions import count

# 数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)


# 创建数据库引擎
engine = create_engine(DB_URI)


# 第一步：创建一个基类（所有 ORM 中的 O，对象模型的超级父类）
Base = declarative_base()

# 第二步：定义 Python 类和表的映射
class Person(Base):
	__tablename__ = 't_person'
	id = Column(name = 'id', type_ = Integer, primary_key = True, autoincrement = True)
	name = Column(name = 'name', type_ = String(255))
	age = Column(name = 'age', type_ = Integer)
	address = Column(type_ = String(255))
	# 创建表之后新加的
	country = Column(type_ = String(50))
	city = Column(type_ = String(50))

	def __str__(self):
		return f'Person(id={self.id}, name={self.name}, age={self.age}, address={self.address}, country={self.country}, city={self.city})'


# 创建 session
# sessionmaker(engine) 返回一个函数
session = sessionmaker(engine)()

def save():
	# 添加
	p1 = Person(name = '张三', age = 18, address = '海淀区', country = '中国', city = '北京')
	p2 = Person(name = '李四', age = 48, address = '海淀区', country = '中国', city = '北京')
	p3 = Person(name = '王五', age = 38, address = '海淀区', country = '中国', city = '北京')
	session.add(p1)
	session.add_all([p2, p3])
	session.commit()

def query():
	p = session.query(Person).first()
	p_list = session.query(Person).all()
	# print(p)
	# print(p_list[1])
	# 查询年龄在18岁以上
	# 打印 SQL 语句，如果加上 first all 才会返回具体的数据
	list = session.query(Person).filter(Person.age >= 18).all()
	# 查询年龄在18岁以上，48岁以下
	list = session.query(Person).filter(Person.age > 18, Person.age < 48).all()
	# list = session.query(Person).filter(and_(Person.age > 18, Person.age < 48)).all()
	# 查询所有农历在18岁以上的人数
	result = session.query(count(Person.id)).filter(Person.age >= 18).first()
	for p in list:
		print(p)
	print(result[0])


def update():
	p = session.query(Person).filter(Person.id == 1).first()
	p.age = 60
	session.commit()

def delete():
	p = session.query(Person).filter(Person.id == 1).first()
	session.delete(p)
	session.commit()


if __name__ == '__main__':
    save()
